﻿namespace Code
{
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;

    using DAO;
    using DAO.DB;

    using Code.Data;

    /// <summary>
    /// Summary description for DataHelper
    /// </summary>
    public class DataHelper
    {
        #region Fields

        /// <summary>
        /// The single instance of this class.
        /// </summary>
        private static DataHelper instance = null;

        #endregion

        #region Constructor

        ///Initializes a new instance of the DataHelper class.
        private DataHelper()
        {
        }

        #endregion

        #region Properties

        /// <summary>
        /// Gets the single instance of the DataHelper class.
        /// </summary>
        public static DataHelper Instance
        {
            get { return DataHelper.instance ?? (DataHelper.instance = new DataHelper()); }
        }

        /// <summary>
        /// Gets the mysql connection string.
        /// </summary>
        public string AccessConnectionString
        {
            get
            {
                return "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=";
            }

        }

        /// <summary>
        /// Gets the mysql connection string.
        /// </summary>
        public string MySqlConnectionString
        {
            get
            {
                return "Server=mysql.elfutec.com.br;Database=elfutec;Uid=elfutec;Pwd=elf147;";
                //return "driver=MySQL ODBC 5.1 Driver;server=mysql.elfutec.com.br;uid=elfutec;pwd=elf147;database=elfutec";
            }
        }

        #endregion

        #region Methods

        /// <summary>
        /// To get a string with all questions.
        /// </summary>
        /// <param name="connector"></param>
        /// <returns></returns>
        public string GetQuestionsWithoutSolution(BaseConnector connector, int id_test, int Sortear_Questoes, out double valueSum, out double approvalPercentage, bool makeTheString, int id_usuario)
        {
            if (Sortear_Questoes > 0)
            {
                return this.GetQuestionsWithoutSolutionRandomized(connector, id_test, Sortear_Questoes, out valueSum, out approvalPercentage, makeTheString, id_usuario);                
            }
            else
            {
                return this.GetQuestionsWithoutSolutionFixed(connector, id_test, out valueSum, out approvalPercentage, makeTheString);                
            }
        }

        /// <summary>
        /// To get the questions for test with fixed questions.
        /// </summary>
        /// <param name="connector"></param>
        /// <param name="id_test"></param>
        /// <param name="valueSum"></param>
        /// <param name="approvalPercentage"></param>
        /// <param name="makeTheString"></param>
        /// <returns></returns>
        public string GetQuestionsWithoutSolutionFixed(BaseConnector connector, int id_test, out double valueSum, out double approvalPercentage, bool makeTheString = true)
        {
            valueSum = 0;
            approvalPercentage = 0;

            List<string> columns = new List<string>();
            int count = 0;

            // Getting the approval percentage
            connector.Open();
            List<string> approval = connector.Query("select approvalPercentage FROM tb_TestDef WHERE id_TestDef=" + id_test, "approvalPercentage");
            if (approval.Count > 0)
            {
                approvalPercentage = double.Parse(approval[0]);
            }
            connector.Close();

            // Getting the questions and the values
            columns.Add("id_question");
            columns.Add("pts");

            List<Question> questionsList = new List<Question>();
            connector.Open();
            List<Dictionary<string, object>> questionNumbersAndValues = connector.QueryWithColumns("select id_question, pts FROM tb_TestsQuestions WHERE id_testDef=" + id_test, columns);
            connector.Close();

            string numberQuery = "where ";
            foreach (Dictionary<string, object> question in questionNumbersAndValues)
            {
                Question questionBuffer = new Question();
                foreach (KeyValuePair<string, object> pair in question)
                {
                    if (pair.Key == "pts")
                    {
                        questionBuffer.Value = (double)pair.Value;
                    }

                    else if (pair.Key == "id_question")
                    {
                        numberQuery += "id_question=" + pair.Value;

                        if (count++ != questionNumbersAndValues.Count - 1)
                        {
                            numberQuery += " OR ";
                        }
                    }
                }

                questionsList.Add(questionBuffer);
            }

            string buffer = "";
            columns = new List<string>();
            columns.Add("id_question");
            columns.Add("question");
            columns.Add("answer1");
            columns.Add("answer2");
            columns.Add("answer3");
            columns.Add("answer4");

            if (numberQuery == "where ")
            {
                return "error";
            }

            connector.Open();
            List<Dictionary<string, object>> questions = connector.QueryWithColumns("select id_question, question, answer1, answer2, answer3, answer4 from tb_QuestionsDef " + numberQuery, columns);
            connector.Close();

            count = 0;
            foreach (Dictionary<string, object> question in questions)
            {
                foreach (KeyValuePair<string, object> pair in question)
                {
                    if (pair.Key == "question")
                    {
                        //questionsList[count].Text = System.Text.Encoding.UTF8.GetString(pair.Value as byte[]);
                        questionsList[count].Text = pair.Value as string;
                    }

                    else if (pair.Key == "answer1")
                    {
                        questionsList[count].Options[0] = pair.Value as string;
                    }

                    else if (pair.Key == "answer2")
                    {
                        questionsList[count].Options[1] = pair.Value as string;
                    }

                    else if (pair.Key == "answer3")
                    {
                        questionsList[count].Options[2] = pair.Value as string;
                    }

                    else if (pair.Key == "answer4")
                    {
                        questionsList[count].Options[3] = pair.Value as string;
                    }
                }

                valueSum += questionsList[count].Value;

                if (makeTheString == true)
                {
                    buffer += questionsList[count].Text + "Ę" + questionsList[count].Options[0] + "Ę" + questionsList[count].Options[1] + "Ę" + questionsList[count].Options[2] + "Ę" + questionsList[count].Options[3] + "Ę" + questionsList[count].Value + "Ą";
                }

                count++;
            }

            if (makeTheString == true)
            {
                buffer = buffer.Remove(buffer.Length - 1);
            }

            connector.Close();
            return buffer;
        }

        /// <summary>
        /// To get the questions for tests with randomized questions.
        /// </summary>
        /// <param name="connector"></param>
        /// <param name="id_test"></param>
        /// <param name="valueSum"></param>
        /// <param name="approvalPercentage"></param>
        /// <param name="makeTheString"></param>
        /// <returns></returns>
        public string GetQuestionsWithoutSolutionRandomized(BaseConnector connector, int id_test, int Sortear_Questoes, out double valueSum, out double approvalPercentage, bool makeTheString, int id_usuario)
        {            
            valueSum = 0;
            approvalPercentage = 0;            
            int count = 0;
            List<Question> questionsList = new List<Question>();
            
            // Getting the approval percentage
            connector.Open();
            List<string> approval = connector.Query("select approvalPercentage FROM tb_TestDef WHERE id_TestDef=" + id_test, "approvalPercentage");
            approvalPercentage = double.Parse(approval[0]);
            connector.Close();

            //return "SELECT * FROM tb_TestStatus WHERE id_test=" + id_test + " AND Id_Usuario=" + id_usuario + " AND Excluido = 0";
            // Getting the questions ids
            connector.Open();
            var reader = connector.QueryWithReader("SELECT * FROM tb_TestStatus WHERE id_test=" + id_test + " AND Id_Usuario=" + id_usuario + " AND Excluido = 0");
            reader.Read();
            
            string[] questionsIds = ((string)reader["Questoes_Sorteadas"]).Split(',');

            string sql = "select id_question, pts FROM tb_TestsQuestions WHERE id_testDef=" + id_test + " AND (1<>1";
            string numberQuery = " (1<>1 ";
            for (int i = 0; i < questionsIds.Length; ++i)
            {
                if (string.IsNullOrEmpty(questionsIds[i]) == false)
                {
                    sql += " OR id_question=" + questionsIds[i];
                    numberQuery += " OR id_question=" + questionsIds[i];
                }
            }
            sql += ")";
            numberQuery += ")";
            reader.Close();
            connector.Close();

            // Getting the question points
            connector.Open();
            reader = connector.QueryWithReader("SELECT * FROM tb_TestsQuestions WHERE id_testDef=" + id_test + " AND " + numberQuery);
            while (reader.Read())
            {
                Question questionBuffer = new Question();
                questionBuffer.Value = (double)reader["pts"];
                questionsList.Add(questionBuffer);
            }

            // Getting the questions and the values
            string buffer = "";
            if (numberQuery == "where ")
            {
                return "error";
            }
            reader.Close();
            connector.Close();

            connector.Open();
            reader = connector.QueryWithReader("SELECT id_question, question, answer1, answer2, answer3, answer4 from tb_QuestionsDef WHERE " + numberQuery);            

            count = 0;
            while (reader.Read())
            {
                //questionsList[count].Text = System.Text.Encoding.UTF8.GetString(pair.Value as byte[]);
                questionsList[count].Id = (int)reader["id_question"];
                questionsList[count].Text = (string)reader["question"];                    
                questionsList[count].Options[0] = (string)reader["answer1"];
                questionsList[count].Options[1] = (string)reader["answer2"];
                questionsList[count].Options[2] = (string)reader["answer3"];
                questionsList[count].Options[3] = (string)reader["answer4"];                
                
                valueSum += questionsList[count].Value;

                /*
                if (makeTheString == true)
                {
                    buffer += questionsList[count].Text + "Ę" + questionsList[count].Options[0] + "Ę" + questionsList[count].Options[1] + "Ę" + questionsList[count].Options[2] + "Ę" + questionsList[count].Options[3] + "Ę" + questionsList[count].Value + "Ą";
                }
                */
                count++;
            }
            
            if (makeTheString == true)
            {
                for (int i = 0; i < questionsIds.Length; ++i)
                {
                    for (int j = 0; j < questionsList.Count; ++j)
                    {                        
                        if (questionsList[j].Id.ToString() == questionsIds[i])
                        {
                            buffer += questionsList[j].Text + "Ę" + questionsList[j].Options[0] + "Ę" + questionsList[j].Options[1] + "Ę" + questionsList[j].Options[2] + "Ę" + questionsList[j].Options[3] + "Ę" + questionsList[j].Value + "Ą";
                            break;
                        }
                    }
                }                

                buffer = buffer.Remove(buffer.Length - 1);
            }

            reader.Close();
            connector.Close();

            return buffer;  
        }

        /// <summary>
        /// To get a string with all questions.
        /// </summary>
        /// <param name="connector"></param>
        /// <returns></returns>
        public string GetQuestions(BaseConnector connector, int id_test)
        {
            // Getting the questions and the values
            List<string> columns = new List<string>();
            columns.Add("id_question");
            columns.Add("pts");

            List<Question> questionsList = new List<Question>();
            connector.Open();
            List<Dictionary<string, object>> questionNumbersAndValues = connector.QueryWithColumns("select id_question, pts FROM tb_TestsQuestions WHERE id_testDef=" + id_test, columns);
            connector.Close();

            int count = 0;
            string numberQuery = "where ";
            foreach (Dictionary<string, object> question in questionNumbersAndValues)
            {
                Question questionBuffer = new Question();
                foreach (KeyValuePair<string, object> pair in question)
                {
                    if (pair.Key == "pts")
                    {
                        questionBuffer.Value = (double)pair.Value;
                    }

                    else if (pair.Key == "id_question")
                    {
                        numberQuery += "id_question=" + pair.Value;

                        if (count++ != questionNumbersAndValues.Count - 1)
                        {
                            numberQuery += " OR ";
                        }
                    }
                }

                questionsList.Add(questionBuffer);
            }

            string buffer = "";
            columns = new List<string>();
            columns.Add("id_question");
            columns.Add("question");
            columns.Add("answer1");
            columns.Add("answer2");
            columns.Add("answer3");
            columns.Add("answer4");
            columns.Add("solution");

            connector.Open();
            List<Dictionary<string, object>> questions = connector.QueryWithColumns("select id_question, question, answer1, answer2, answer3, answer4, solution from tb_QuestionsDef " + numberQuery, columns);
            connector.Close();

            count = 0;
            foreach (Dictionary<string, object> question in questions)
            {
                foreach (KeyValuePair<string, object> pair in question)
                {
                    if (pair.Key == "question")
                    {
                        questionsList[count].Text = pair.Value as string;
                    }

                    else if (pair.Key == "answer1")
                    {
                        questionsList[count].Options[0] = pair.Value as string;
                    }

                    else if (pair.Key == "answer2")
                    {
                        questionsList[count].Options[1] = pair.Value as string;
                    }

                    else if (pair.Key == "answer3")
                    {
                        questionsList[count].Options[2] = pair.Value as string;
                    }

                    else if (pair.Key == "answer4")
                    {
                        questionsList[count].Options[3] = pair.Value as string;
                    }

                    else if (pair.Key == "solution")
                    {
                        questionsList[count].Answer = (int)pair.Value;
                    }
                }

                buffer += questionsList[count].Text + "Ę" + questionsList[count].Options[0] + "Ę" + questionsList[count].Options[1] + "Ę" + questionsList[count].Options[2] + "Ę" + questionsList[count].Options[3] + "Ę" + this.Encrypt(questionsList[count].Answer) + "Ę" + questionsList[count].Value + "Ą";
                count++;
            }

            buffer = buffer.Remove(buffer.Length - 1);
            connector.Close();
            return buffer;
        }

        /// <summary>
        /// To get a string with all questions.
        /// </summary>
        /// <param name="connector"></param>
        /// <returns></returns>
        public string GetQuestions(BaseConnector connector, int id_test, int id_student, string questions_ids)
        {
            List<Question> questionsList = new List<Question>();
            connector.Open();

            // Getting the questions and the values
            string query = "SELECT id_question, pts FROM tb_TestsQuestions WHERE id_testDef=" + id_test + " ";

            if (questions_ids != "-1")
            {
                query += " AND (1<>1 ";
                string[] random_questions = questions_ids.Split(',');
                for (int i = 0; i < random_questions.Length; ++i)
                {
                    if (string.IsNullOrEmpty(random_questions[i].Trim()) == false)
                    {
                        query += " OR id_question=" + random_questions[i];
                    }
                }

                query += " )";
            }

            string numberQuery = "WHERE ";
            var reader = connector.QueryWithReader(query);
            reader.Read();            
            while (true)
            {                
                Question questionBuffer = new Question();
                questionBuffer.Value = (double)reader["pts"];
                numberQuery += "id_question=" + (int)reader["id_question"];
                questionsList.Add(questionBuffer);

                if (reader.Read() == true)
                {
                    numberQuery += " OR ";
                }
                else
                {
                    break;
                }                
            }

            string buffer = "";
            query = "SELECT id_question, question, answer1, answer2, answer3, answer4, solution FROM tb_QuestionsDef ";
            query += numberQuery;

            reader.Close();
            reader = connector.QueryWithReader(query);

            if (questions_ids == "-1")
            {       
                
                int count = 0;
                while (reader.Read())
                {
                    questionsList[count].Id = (int)reader["id_question"];
                    questionsList[count].Text = (string)reader["question"];
                    questionsList[count].Options[0] = (string)reader["answer1"];
                    questionsList[count].Options[1] = (string)reader["answer2"];
                    questionsList[count].Options[2] = (string)reader["answer3"];
                    questionsList[count].Options[3] = (string)reader["answer4"];
                    questionsList[count].Answer = (int)reader["solution"];

                    buffer += questionsList[count].Text + "Ę" + questionsList[count].Options[0] + "Ę" + questionsList[count].Options[1] + "Ę" + questionsList[count].Options[2] + "Ę" + questionsList[count].Options[3] + "Ę" + this.Encrypt(questionsList[count].Answer) + "Ę" + questionsList[count].Value + "Ę" + questionsList[count].Id + "Ą";
                    count++;
                }
            }
            else
            {
                int count = 0;
                while (reader.Read())
                {
                    questionsList[count].Id = (int)reader["id_question"];
                    questionsList[count].Text = (string)reader["question"];
                    questionsList[count].Options[0] = (string)reader["answer1"];
                    questionsList[count].Options[1] = (string)reader["answer2"];
                    questionsList[count].Options[2] = (string)reader["answer3"];
                    questionsList[count].Options[3] = (string)reader["answer4"];
                    questionsList[count].Answer = (int)reader["solution"];
                    count++;
                }

                string [] questionsIds = questions_ids.Split(',');
                for (int i = 0; i < questionsIds.Length; ++i)
                {
                    for (int j = 0; j < questionsList.Count; ++j)
                    {
                        if (questionsList[j].Id.ToString() == questionsIds[i])
                        {
                            buffer += questionsList[j].Text + "Ę" + questionsList[j].Options[0] + "Ę" + questionsList[j].Options[1] + "Ę" + questionsList[j].Options[2] + "Ę" + questionsList[j].Options[3] + "Ę" + this.Encrypt(questionsList[j].Answer) + "Ę" + questionsList[j].Value + "Ę" + questionsList[j].Id + "Ą";
                            break;
                        }
                    }
                }
            }

            buffer = buffer.Remove(buffer.Length - 1);

            reader.Close();
            connector.Close();            
            return buffer;
        }

        /// <summary>
        /// Tp encrypt the correct question number.
        /// </summary>
        /// <param name="number">The question number.</param>
        /// <returns>The question number as an encrypted string.</returns>
        public string Encrypt(int number)
        {
            string data = "";
            Random random = new Random();
            data += "" + (char)random.Next(65, 90);
            data += "" + (char)random.Next(65, 90);
            data += "" + random.Next(0, 9);
            data += "" + (char)random.Next(65, 90);

            switch (number)
            {
                case 1:
                    data += "O";
                    break;

                case 2:
                    data += "T";
                    break;

                case 3:
                    data += "H";
                    break;

                case 4:
                    data += "F";
                    break;
            }

            data += "" + (char)random.Next(65, 90);
            data += "" + random.Next(0, 9);

            return data;
        }

        /// <summary>
        /// To decrypt the question answer.
        /// </summary>
        /// <param name="text"></param>
        /// <returns></returns>
        public int Decrypt(string text)
        {
            char secret = text[4];

            switch (secret)
            {
                case 'O':
                    return 1;

                case 'T':
                    return 2;

                case 'H':
                    return 3;

                case 'F':
                    return 4;
            }

            return 0;
        }

        #endregion    
    }
}